#!/usr/bin/env node
/**
 * 数据库初始化脚本
 * 用于在服务器上创建和初始化SQLite数据库
 */

const sqlite3 = require('sqlite3').verbose();
const path = require('path');
const fs = require('fs');
const bcrypt = require('bcryptjs');
const { v4: uuidv4 } = require('uuid');

// 确保数据目录存在
const dataDir = path.join(__dirname, '..', 'data');
const dbPath = path.join(dataDir, 'database.sqlite');

if (!fs.existsSync(dataDir)) {
    console.log('创建数据目录:', dataDir);
    fs.mkdirSync(dataDir, { recursive: true });
}

console.log('初始化数据库:', dbPath);

const db = new sqlite3.Database(dbPath);

// 创建表结构
const createTables = () => {
    return new Promise((resolve, reject) => {
        db.serialize(() => {
            // 创建用户表
            db.run(`
                CREATE TABLE IF NOT EXISTS users (
                    id TEXT PRIMARY KEY,
                    username TEXT UNIQUE NOT NULL,
                    email TEXT UNIQUE NOT NULL,
                    password TEXT NOT NULL,
                    role TEXT DEFAULT 'editor',
                    name TEXT NOT NULL,
                    avatar TEXT,
                    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
                    updated_at DATETIME DEFAULT CURRENT_TIMESTAMP
                )
            `);

            // 创建新闻表（使用与db.ts相同的结构）
            db.run(`
                CREATE TABLE IF NOT EXISTS news (
                    id TEXT PRIMARY KEY,
                    title TEXT NOT NULL,
                    summary TEXT NOT NULL,
                    content TEXT NOT NULL,
                    image TEXT,
                    images TEXT,
                    category TEXT DEFAULT 'news',
                    tags TEXT,
                    author TEXT NOT NULL,
                    publish_date TEXT NOT NULL,
                    is_published INTEGER DEFAULT 1,
                    is_pinned INTEGER DEFAULT 0,
                    view_count INTEGER DEFAULT 0,
                    created_at TEXT DEFAULT CURRENT_TIMESTAMP,
                    updated_at TEXT DEFAULT CURRENT_TIMESTAMP
                )
            `);

            // 创建联系表（支持多种联系方式）
            db.run(`
                CREATE TABLE IF NOT EXISTS contacts (
                    id TEXT PRIMARY KEY,
                    type TEXT NOT NULL,
                    title TEXT NOT NULL,
                    description TEXT,
                    content TEXT,
                    qr_code TEXT,
                    is_visible INTEGER DEFAULT 1,
                    order_num INTEGER DEFAULT 0,
                    created_at TEXT DEFAULT CURRENT_TIMESTAMP,
                    updated_at TEXT DEFAULT CURRENT_TIMESTAMP
                )
            `);

            // 创建成员表
            db.run(`
                CREATE TABLE IF NOT EXISTS members (
                    id TEXT PRIMARY KEY,
                    name TEXT NOT NULL,
                    role TEXT NOT NULL,
                    title TEXT,
                    email TEXT,
                    phone TEXT,
                    avatar TEXT,
                    bio TEXT,
                    research_areas TEXT,
                    homepage TEXT,
                    order_index INTEGER DEFAULT 0,
                    is_active INTEGER DEFAULT 1,
                    join_date TEXT,
                    graduation_date TEXT,
                    current_position TEXT,
                    created_at TEXT DEFAULT CURRENT_TIMESTAMP,
                    updated_at TEXT DEFAULT CURRENT_TIMESTAMP
                )
            `);

            // 创建项目表
            db.run(`
                CREATE TABLE IF NOT EXISTS projects (
                    id TEXT PRIMARY KEY,
                    title TEXT NOT NULL,
                    description TEXT NOT NULL,
                    full_description TEXT,
                    image TEXT,
                    images TEXT,
                    status TEXT DEFAULT 'ongoing',
                    category TEXT NOT NULL,
                    tags TEXT,
                    start_date TEXT NOT NULL,
                    end_date TEXT,
                    members TEXT,
                    achievements TEXT,
                    links TEXT,
                    order_index INTEGER DEFAULT 0,
                    is_published INTEGER DEFAULT 1,
                    created_at TEXT DEFAULT CURRENT_TIMESTAMP,
                    updated_at TEXT DEFAULT CURRENT_TIMESTAMP
                )
            `);

            // 创建招新表
            db.run(`
                CREATE TABLE IF NOT EXISTS recruitments (
                    id TEXT PRIMARY KEY,
                    title TEXT NOT NULL,
                    description TEXT NOT NULL,
                    requirements TEXT,
                    responsibilities TEXT,
                    positions INTEGER DEFAULT 1,
                    deadline TEXT,
                    contact_email TEXT NOT NULL,
                    contact_phone TEXT,
                    is_active INTEGER DEFAULT 1,
                    category TEXT DEFAULT 'undergraduate',
                    application_form TEXT,
                    created_at TEXT DEFAULT CURRENT_TIMESTAMP,
                    updated_at TEXT DEFAULT CURRENT_TIMESTAMP
                )
            `);

            // 创建申请表
            db.run(`
                CREATE TABLE IF NOT EXISTS applications (
                    id TEXT PRIMARY KEY,
                    recruitment_id TEXT NOT NULL,
                    applicant_name TEXT NOT NULL,
                    applicant_email TEXT NOT NULL,
                    applicant_phone TEXT,
                    form_data TEXT NOT NULL,
                    attachments TEXT,
                    status TEXT DEFAULT 'pending',
                    notes TEXT,
                    reviewed_by TEXT,
                    reviewed_at TEXT,
                    created_at TEXT DEFAULT CURRENT_TIMESTAMP,
                    updated_at TEXT DEFAULT CURRENT_TIMESTAMP,
                    FOREIGN KEY (recruitment_id) REFERENCES recruitments (id)
                )
            `);

            // 创建媒体文件表
            db.run(`
                CREATE TABLE IF NOT EXISTS media_files (
                    id TEXT PRIMARY KEY,
                    filename TEXT NOT NULL,
                    original_name TEXT NOT NULL,
                    path TEXT NOT NULL,
                    url TEXT NOT NULL,
                    mime_type TEXT NOT NULL,
                    size INTEGER NOT NULL,
                    alt TEXT,
                    caption TEXT,
                    category TEXT,
                    uploaded_by TEXT,
                    created_at TEXT DEFAULT CURRENT_TIMESTAMP,
                    updated_at TEXT DEFAULT CURRENT_TIMESTAMP
                )
            `);

            // 创建网站配置表
            db.run(`
                CREATE TABLE IF NOT EXISTS site_config (
                    id TEXT PRIMARY KEY DEFAULT 'main',
                    title TEXT DEFAULT '机器人创新实验室',
                    subtitle TEXT,
                    description TEXT DEFAULT '专注于机器人技术研发与创新',
                    logo TEXT,
                    favicon TEXT,
                    contact_info TEXT,
                    theme TEXT,
                    seo TEXT,
                    navigation TEXT,
                    footer TEXT,
                    analytics TEXT,
                    carousel_settings TEXT,
                    updated_at TEXT DEFAULT CURRENT_TIMESTAMP
                )
            `, (err) => {
                if (err) {
                    reject(err);
                } else {
                    console.log('✓ 数据库表创建完成');
                    resolve();
                }
            });
        });
    });
};

// 创建默认管理员用户
const createDefaultAdmin = async () => {
    const adminUsername = process.env.ADMIN_USERNAME || 'admin';
    const adminPassword = process.env.ADMIN_PASSWORD || 'admin123';
    
    // 检查是否已存在管理员用户
    const checkAdmin = () => {
        return new Promise((resolve, reject) => {
            db.get('SELECT id FROM users WHERE username = ?', [adminUsername], (err, row) => {
                if (err) reject(err);
                else resolve(row);
            });
        });
    };
    
    const existingAdmin = await checkAdmin();
    
    if (existingAdmin) {
        console.log('✓ 管理员用户已存在:', adminUsername);
        return;
    }
    
    // 创建新管理员用户
    const hashedPassword = await bcrypt.hash(adminPassword, 10);
    const adminId = uuidv4();
    
    return new Promise((resolve, reject) => {
        db.run(
            'INSERT INTO users (id, username, email, password, role, name) VALUES (?, ?, ?, ?, ?, ?)',
            [adminId, adminUsername, 'admin@robotlab.com', hashedPassword, 'admin', '系统管理员'],
            (err) => {
                if (err) {
                    reject(err);
                } else {
                    console.log('✓ 默认管理员用户创建完成');
                    console.log(`   用户名: ${adminUsername}`);
                    console.log(`   密码: ${adminPassword}`);
                    console.log('   ⚠️  请在部署后立即修改密码！');
                    resolve();
                }
            }
        );
    });
};

// 初始化示例数据
const createSampleData = () => {
    return new Promise((resolve, reject) => {
        // 检查是否已有新闻数据
        db.get('SELECT COUNT(*) as count FROM news', (err, row) => {
            if (err) {
                reject(err);
                return;
            }
            
            if (row.count > 0) {
                console.log('✓ 已有新闻数据，跳过示例数据创建');
                resolve();
                return;
            }
            
            // 创建示例新闻
            const sampleNews = {
                id: uuidv4(),
                title: '欢迎访问机器人创新实验室',
                summary: '欢迎访问我们的网站',
                content: '这是一条示例新闻内容。您可以在管理后台创建和编辑新闻内容。',
                author: '管理员',
                publish_date: new Date().toISOString().split('T')[0],
                is_published: 1,
                is_pinned: 0
            };
            
            db.run(
                'INSERT INTO news (id, title, summary, content, author, publish_date, is_published, is_pinned) VALUES (?, ?, ?, ?, ?, ?, ?, ?)',
                [sampleNews.id, sampleNews.title, sampleNews.summary, sampleNews.content, sampleNews.author, sampleNews.publish_date, sampleNews.is_published, sampleNews.is_pinned],
                (err) => {
                    if (err) {
                        reject(err);
                    } else {
                        console.log('✓ 示例新闻数据创建完成');
                        resolve();
                    }
                }
            );
        });
    });
};

// 创建默认联系方式数据（仅在首次初始化时）
const createDefaultContacts = () => {
    return new Promise((resolve, reject) => {
        // 检查是否已有联系方式数据
        db.get('SELECT COUNT(*) as count FROM contacts', (err, row) => {
            if (err) {
                reject(err);
                return;
            }
            
            if (row.count > 0) {
                console.log('✓ 已有联系方式数据，跳过默认联系方式创建');
                resolve();
                return;
            }
            
            // 创建默认联系方式（仅首次初始化时）
            const defaultContacts = [
                {
                    id: 'wechat-group-default',
                    type: 'wechat_group',
                    title: '技术交流群',
                    description: '讨论机器人技术，分享学习资源',
                    content: '扫码加入微信群',
                    qr_code: '/api/uploads/wechat-group-qr.svg',
                    is_visible: 1,
                    order_num: 1
                },
                {
                    id: 'wechat-official-default',
                    type: 'wechat_official',
                    title: '实验室公众号',
                    description: '获取最新动态和技术文章',
                    content: '关注我们的微信公众号',
                    qr_code: '/api/uploads/wechat-official-qr.svg',
                    is_visible: 1,
                    order_num: 2
                },
                {
                    id: 'email-default',
                    type: 'email',
                    title: '联系邮箱',
                    description: '商务合作、学术交流',
                    content: 'contact@roboticslab.com',
                    qr_code: null,
                    is_visible: 1,
                    order_num: 3
                }
            ];
            
            const stmt = db.prepare(`INSERT INTO contacts (id, type, title, description, content, qr_code, is_visible, order_num) VALUES (?, ?, ?, ?, ?, ?, ?, ?)`);
            
            let completed = 0;
            defaultContacts.forEach(contact => {
                stmt.run([
                    contact.id, 
                    contact.type, 
                    contact.title, 
                    contact.description, 
                    contact.content, 
                    contact.qr_code, 
                    contact.is_visible, 
                    contact.order_num
                ], (err) => {
                    if (err) {
                        reject(err);
                        return;
                    }
                    completed++;
                    if (completed === defaultContacts.length) {
                        stmt.finalize();
                        console.log('✓ 默认联系方式数据创建完成');
                        resolve();
                    }
                });
            });
        });
    });
};

// 创建默认网站配置
const createDefaultSiteConfig = () => {
    return new Promise((resolve, reject) => {
        // 检查是否已有配置数据
        db.get('SELECT COUNT(*) as count FROM site_config', (err, row) => {
            if (err) {
                reject(err);
                return;
            }
            
            if (row.count > 0) {
                console.log('✓ 已有网站配置，跳过默认配置创建');
                resolve();
                return;
            }
            
            // 创建默认网站配置
            const defaultConfig = {
                id: 'main',
                title: '机器人创新实验室',
                description: '致力于机器人技术研发与创新，培养优秀的机器人工程师',
                contact_info: JSON.stringify({
                    email: "contact@roboticslab.com", 
                    phone: "+86-10-12345678", 
                    address: "某某大学某某楼某某室"
                }),
                theme: JSON.stringify({
                    primaryColor: "#3b82f6", 
                    secondaryColor: "#64748b", 
                    backgroundColor: "#ffffff", 
                    textColor: "#1e293b"
                }),
                navigation: JSON.stringify([
                    {id: "home", label: "首页", href: "/"}, 
                    {id: "about", label: "关于我们", href: "/about"}, 
                    {id: "members", label: "团队成员", href: "/members"}, 
                    {id: "projects", label: "项目成果", href: "/projects"}, 
                    {id: "news", label: "新闻动态", href: "/news"}, 
                    {id: "recruitment", label: "加入我们", href: "/recruitment"}
                ]),
                footer: JSON.stringify({
                    sections: [{
                        title: "快速链接", 
                        links: [
                            {label: "关于我们", href: "/about"}, 
                            {label: "联系我们", href: "/contact"}
                        ]
                    }], 
                    copyright: "© 2024 机器人创新实验室. All rights reserved."
                })
            };
            
            db.run(
                'INSERT INTO site_config (id, title, description, contact_info, theme, navigation, footer) VALUES (?, ?, ?, ?, ?, ?, ?)',
                [defaultConfig.id, defaultConfig.title, defaultConfig.description, defaultConfig.contact_info, defaultConfig.theme, defaultConfig.navigation, defaultConfig.footer],
                (err) => {
                    if (err) {
                        reject(err);
                    } else {
                        console.log('✓ 默认网站配置创建完成');
                        resolve();
                    }
                }
            );
        });
    });
};

// 主初始化函数
async function initDatabase() {
    try {
        await createTables();
        await createDefaultAdmin();
        await createSampleData();
        await createDefaultContacts();
        await createDefaultSiteConfig();
        
        console.log('\n🎉 数据库初始化完成！');
        console.log('\n下一步:');
        console.log('1. 运行: npm run build');
        console.log('2. 启动应用: npm run pm2');
        console.log('3. 访问管理后台: http://你的域名/admin');
        
    } catch (error) {
        console.error('❌ 数据库初始化失败:', error);
        process.exit(1);
    } finally {
        db.close();
    }
}

// 运行初始化
initDatabase();